package com.meetingshow.common.base.dao;

import java.io.Serializable;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.time.DateUtils;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import com.meetingshow.common.base.dao.model.BasePO;
import com.meetingshow.common.base.dao.model.OrQueryItem;
import com.meetingshow.common.base.dao.model.OrQueryItemList;
import com.meetingshow.common.base.dao.model.QueryStruct;
import com.meetingshow.common.base.dao.model.Relation;
import com.meetingshow.common.base.dao.model.RelationList;
import com.meetingshow.common.base.service.model.QueryResults;
@SuppressWarnings({"unchecked", "rawtypes"})
public abstract class BaseDAO extends HibernateDaoSupport {

    protected Serializable saveBaseObj(BasePO basePO) throws Exception {
        return getHibernateTemplate().save(basePO);
    }

    protected void updateBaseObj(BasePO basePO) throws Exception {
        getHibernateTemplate().update(basePO);
    }

    protected void deleteBaseObj(BasePO basePO) throws Exception {
        getHibernateTemplate().delete(basePO);
    }

    
    protected <T> T queryById(Class<T> cls, Serializable id) throws Exception {
        Object obj = null;
        obj = getHibernateTemplate().get(cls, id);
        return (T) obj;
    }
    
    
    protected <T> List<T> findByParameter(QueryStruct queryStruct)
            throws Exception {
        RelationList relationList = queryStruct.getRelationList();
        Map<String, ?> exactMap = queryStruct.getExactMap();
        OrQueryItemList orQueryItemList = queryStruct.getOrQueryItemList();
        RelationList dateList = queryStruct.getDateList();
        Map<String, ?> mistinessMap = queryStruct.getMistinessMap();
        List<String> returnTableList = queryStruct.getReturnObjectList();
        List<String> tableList = queryStruct.getTableList();
        Relation queryOrder = queryStruct.getQueryOrder();
        final String firstResultNo = queryStruct.getFirstResultNo();
        final String resultsPerGroup = queryStruct.getResultsPerGroup();
        String groupBy = queryStruct.getGroupBy();

        List queryList = new ArrayList();

        String hql = "select ";

        if (returnTableList != null & returnTableList.size() > 0) {
            String returnObjectStr = "";

            for (int i = 0; i < returnTableList.size(); i++) {
                String temp = (String) returnTableList.get(i);
                // 返回对象如果不是表的别名，而是某个字段时，别名要转小写，但是字段不能转小写
                if (temp.indexOf(".") != -1) {
                    String tableAlias = temp.substring(0, temp.indexOf("."));
                    String field = temp.substring(temp.indexOf("."),
                            temp.length());
                    temp = tableAlias.toLowerCase() + field;
                } else {
                    temp = temp.toLowerCase();
                }

                returnObjectStr += temp + ",";
            }

            if (returnObjectStr.indexOf(",") != -1)
                returnObjectStr = returnObjectStr.substring(0,
                        returnObjectStr.length() - 1);

            hql += returnObjectStr;
        } else {
            Exception e = new Exception("returnTableList is empty.");
            throw e;
        }

        hql = hql + " from ";
        if (tableList != null & tableList.size() > 0) {
            String temp = (String) tableList.get(0);
            hql = hql + temp + " " + temp.toLowerCase() + " ";
            for (int i = 1; i < tableList.size(); i++) {
                temp = (String) tableList.get(i);
                hql = hql + "," + temp + " " + temp.toLowerCase() + " ";

            }
        } else {
            Exception e = new Exception("tableList is empty.");
            throw e;
        }
        hql = hql + " " + " where 1=1 ";

        // 模糊查询
        if (null != mistinessMap) {
            for (Iterator it = mistinessMap.keySet().iterator(); it.hasNext();) {

                String key = (String) it.next();

                Object value = mistinessMap.get(key);

                // hql += " and " + key + "  like '%" + value.toString() +
                // "%' ";
                hql += " and " + key + "  like " + " (?) ";
                queryList.add("%" + value.toString() + "%");
            }
        }

        // 精确查询
        if (null != exactMap) {
            for (Iterator it = exactMap.keySet().iterator(); it.hasNext();) {

                String key = (String) it.next();

                Object value = exactMap.get(key);

                // hql += " and " + key + "  ='" + value.toString() + "' ";
                hql += " and " + key + "  = " + " (?) ";
                queryList.add(value);

            }
        }

        // 日期查询
        if (null != dateList) {
            for (int i = 0; i < dateList.size(); i++) {
                Relation relation = dateList.getAt(i);
                // SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                hql += " and " + relation.getObjName() + " "
                        + relation.getRelation() + " " + " (?) ";

                Date date = DateUtils.parseDate(relation.getValue(), new String[]{"yyyy-MM-dd HH:mm:ss", "yyyy-MM-dd"});
                queryList.add(date);

            }
        }

        // 关系查询
        if (null != relationList) {
            for (int j = 0; j < relationList.size(); j++) {
                Relation relation = relationList.getAt(j);
                hql += " and " + relation.getObjName() + relation.getRelation()
                        + relation.getValue();
                // hql += " and " + relation.getObjName() +
                // relation.getRelation() + " (?) ";
                // queryList.add(relation.getValue());

            }
        }
        if (null != orQueryItemList) {
            for (int i = 0; i < orQueryItemList.size(); i++) {
                OrQueryItem orQueryItem = orQueryItemList.getAt(i);
                RelationList orExactList = orQueryItem.getOrExactList();
                RelationList orMistinessList = orQueryItem.getOrMistinessList();
                RelationList orRelationList = orQueryItem.getOrRelationList();
                // 或查询(精确查询)
                String hqlorExact = "";
                if (orExactList != null && orExactList.size() > 0) {
                    hqlorExact = hqlorExact + " and (";
                    for (int j = 0; j < orExactList.size(); j++) {
                        Relation relation = orExactList.getAt(j);
                        // hqlorExact += " " + relation.getObjName() + " = '" +
                        // relation.getValue() + "' or ";
                        hqlorExact += " " + relation.getObjName() + " = "
                                + " (?) " + " or ";
                        queryList.add(relation.getValue());

                    }
                    if (!(orMistinessList != null && orMistinessList.size() > 0)
                            && !(orRelationList != null && orRelationList
                                    .size() > 0)) {
                        hqlorExact = hqlorExact.substring(0,
                                hqlorExact.length() - 3)
                                + " ) ";
                    }
                }
                hql = hql + hqlorExact;

                String hqlorMistiness = "";
                String left = "%";
                String right = "%";
                // 或查询（模糊查询）
                if (orMistinessList != null && orMistinessList.size() > 0) {
                    if (!(orExactList != null && orExactList.size() > 0)) {
                        hqlorMistiness = hqlorMistiness + " and (";
                    }
                    for (int j = 0; j < orMistinessList.size(); j++) {
                        Relation relation = orMistinessList.getAt(j);
                        if (relation.getRelation() != null
                                && relation.getRelation().equals("left")) {
                            left = "";
                        } else if (relation.getRelation() != null
                                && relation.getRelation().equals("right")) {
                            right = "";
                        }

                        // hqlorMistiness += " " + relation.getObjName() +
                        // " like '" + left + relation.getValue() + right +
                        // "' or ";
                        hqlorMistiness += " " + relation.getObjName()
                                + " like " + " (?) " + " or ";
                        queryList.add(left + relation.getValue() + right);

                    }

                    if (!(orRelationList != null && orRelationList.size() > 0)) {
                        hqlorMistiness = hqlorMistiness.substring(0,
                                hqlorMistiness.length() - 3) + " ) ";
                    }

                }
                hql = hql + hqlorMistiness;

                // 或查询（关系）
                String hqlorRelation = "";

                if (orRelationList != null && orRelationList.size() > 0) {
                    if (!(orExactList != null && orExactList.size() > 0)
                            && !(orMistinessList != null && orMistinessList
                                    .size() > 0)) {
                        hqlorRelation = hqlorRelation + " and (";
                    }
                    for (int k = 0; k < orRelationList.size(); k++) {
                        Relation relation = orRelationList.getAt(k);

                        hqlorRelation += " " + relation.getObjName() + " "
                                + relation.getRelation() + " "
                                + relation.getValue() + " or ";

                    }
                    hqlorRelation = hqlorRelation.substring(0,
                            hqlorRelation.length() - 3)
                            + " ) ";
                }
                hql = hql + hqlorRelation;
            }
        }
        hql = hql + " and 1=1  ";
        // 排序
        if (queryOrder != null) {
            hql += " Order by " + queryOrder.getObjName() + " "
                    + queryOrder.getRelation();
        }

        if (null != groupBy) {
            hql = hql + " group by " + groupBy;
        }

        final String queryString = hql;

        final List hqlList = queryList;

        List list = getHibernateTemplate().executeFind(new HibernateCallback() {
            public Object doInHibernate(Session session)
                    throws HibernateException, SQLException {

                Query q = session.createQuery(queryString);
                if (resultsPerGroup != null) {
                    q.setMaxResults(new Integer(resultsPerGroup).intValue());
                }
                if (firstResultNo != null) {
                    q.setFirstResult(new Integer(firstResultNo).intValue());
                }
                for (int i = 0; i < hqlList.size(); i++) {
                    q.setParameter(i, hqlList.get(i));

                }
                List page = q.list();
                return page;
            }
        });

        // List list = getHibernateTemplate().find(hql);

        return list;

    }

    /**
     * 分组统计
     * 
     * @param <T>
     *            返回值的泛型
     * @param queryStruct
     *            查询参数结构体
     * @return List<T> 统计结果
     * @throws Exception
     */
    protected <T> List<T> countByParameterGroup(QueryStruct queryStruct)
            throws Exception {

        RelationList relationList = queryStruct.getRelationList();
        Map<String, ?> exactMap = queryStruct.getExactMap();
        OrQueryItemList orQueryItemList = queryStruct.getOrQueryItemList();
        RelationList dateList = queryStruct.getDateList();
        Map<String, ?> mistinessMap = queryStruct.getMistinessMap();
        // List returnTableList = queryStruct.getReturnTableList();
        List<String> tableList = queryStruct.getTableList();
        List<String> returnTableList = queryStruct.getReturnObjectList();
        final String groupBy = queryStruct.getGroupBy();
        final String firstResultNo = queryStruct.getFirstResultNo();
        final String resultsPerGroup = queryStruct.getResultsPerGroup();

        String countobject = "*";

        if (returnTableList != null & returnTableList.size() > 0) {
            // 楼毅修改于2010-06-13
            countobject = ((String) returnTableList.get(0)); // 主要针对返回对象有"distinct"的情况
            // 返回对象如果不是表的别名，而是某个字段时，别名要转小写，但是字段不能转小写
            if (countobject.indexOf(".") != -1) {
                String tableAlias = countobject.substring(0,
                        countobject.indexOf("."));
                String field = countobject.substring(countobject.indexOf("."),
                        countobject.length());
                countobject = tableAlias.toLowerCase() + field;
            } else {
                countobject = countobject.toLowerCase();
            }
        }

        String hql = "select count(" + countobject + ") ";
        if (null != groupBy) {
            hql = hql + " , " + groupBy + " ";
        }

        hql = hql + "from ";

        if (tableList != null & tableList.size() > 0) {
            String temp = (String) tableList.get(0);
            hql = hql + temp + " " + temp.toLowerCase() + " ";
            for (int i = 1; i < tableList.size(); i++) {
                temp = (String) tableList.get(i);
                hql = hql + "," + temp + " " + temp.toLowerCase() + " ";
            }
        } else {
            Exception e = new Exception("tableList is empty.");
            throw e;
        }
        hql = hql + " " + " where 1=1 ";

        // 包含查询
        List queryList = new ArrayList();
        // 模糊查询
        if (null != mistinessMap) {
            for (Iterator it = mistinessMap.keySet().iterator(); it.hasNext();) {

                String key = (String) it.next();

                Object value = mistinessMap.get(key);

                // hql += " and " + key + "  like '%" + value.toString() +
                // "%' ";
                hql += " and " + key + "  like " + " (?) ";
                queryList.add("%" + value.toString() + "%");

            }
        }

        // 精确查询
        if (null != exactMap) {
            for (Iterator it = exactMap.keySet().iterator(); it.hasNext();) {

                String key = (String) it.next();

                Object value = exactMap.get(key);

                // hql += " and " + key + "  ='" + value.toString() + "' ";
                hql += " and " + key + "  = " + " (?) ";
                queryList.add(value);

            }
        }

        // 日期查询
        if (null != dateList) {
            for (int i = 0; i < dateList.size(); i++) {
                Relation relation = dateList.getAt(i);
                // SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                hql += " and " + relation.getObjName() + " "
                        + relation.getRelation() + " " + " (?) ";

                Date date = DateUtils.parseDate(relation.getValue(), new String[]{"yyyy-MM-dd HH:mm:ss", "yyyy-MM-dd"});
                queryList.add(date);

            }
        }

        // 关系查询
        if (null != relationList) {
            for (int j = 0; j < relationList.size(); j++) {
                Relation relation = relationList.getAt(j);
                hql += " and " + relation.getObjName() + relation.getRelation()
                        + relation.getValue();
            }
        }

        if (null != orQueryItemList) {
            for (int i = 0; i < orQueryItemList.size(); i++) {
                OrQueryItem orQueryItem = orQueryItemList.getAt(i);
                RelationList orExactList = orQueryItem.getOrExactList();
                RelationList orMistinessList = orQueryItem.getOrMistinessList();
                RelationList orRelationList = orQueryItem.getOrRelationList();

                // 或查询(精确查询)
                String hqlorExact = "";
                if (orExactList != null && orExactList.size() > 0) {
                    hqlorExact = hqlorExact + " and (";
                    for (int j = 0; j < orExactList.size(); j++) {
                        Relation relation = orExactList.getAt(j);

                        // hqlorExact += " " + relation.getObjName() + " = '" +
                        // relation.getValue() + "' or ";
                        hqlorExact += " " + relation.getObjName() + " = "
                                + " (?) " + " or ";
                        queryList.add(relation.getValue());

                    }
                    if (!(orMistinessList != null && orMistinessList.size() > 0)
                            && !(orRelationList != null && orRelationList
                                    .size() > 0)) {
                        hqlorExact = hqlorExact.substring(0,
                                hqlorExact.length() - 3)
                                + " ) ";
                    }
                }
                hql = hql + hqlorExact;

                String hqlorMistiness = "";
                String left = "%";
                String right = "%";

                // 或查询（模糊查询）
                if (orMistinessList != null && orMistinessList.size() > 0) {
                    if (!(orExactList != null && orExactList.size() > 0)) {
                        hqlorMistiness = hqlorMistiness + " and (";
                    }
                    for (int j = 0; j < orMistinessList.size(); j++) {
                        Relation relation = orMistinessList.getAt(j);
                        if (relation.getRelation() != null
                                && relation.getRelation().equals("left")) {
                            left = "";
                        } else if (relation.getRelation() != null
                                && relation.getRelation().equals("right")) {
                            right = "";
                        }

                        // hqlorMistiness += " " + relation.getObjName() +
                        // " like '" + left + relation.getValue() + right +
                        // "' or ";
                        hqlorMistiness += " " + relation.getObjName()
                                + " like " + " (?) " + " or ";
                        queryList.add(left + relation.getValue() + right);

                    }

                    if (!(orRelationList != null && orRelationList.size() > 0)) {
                        hqlorMistiness = hqlorMistiness.substring(0,
                                hqlorMistiness.length() - 3) + " ) ";
                    }

                }
                hql = hql + hqlorMistiness;

                // 或查询（关系）
                String hqlorRelation = "";

                if (orRelationList != null && orRelationList.size() > 0) {
                    if (!(orExactList != null && orExactList.size() > 0)
                            && !(orMistinessList != null && orMistinessList
                                    .size() > 0)) {
                        hqlorRelation = hqlorRelation + " and (";
                    }
                    for (int k = 0; k < orRelationList.size(); k++) {
                        Relation relation = orRelationList.getAt(k);

                        hqlorRelation += " " + relation.getObjName() + " "
                                + relation.getRelation() + " "
                                + relation.getValue() + " or ";

                    }
                    hqlorRelation = hqlorRelation.substring(0,
                            hqlorRelation.length() - 3)
                            + " ) ";
                }
                hql = hql + hqlorRelation;
            }
        }
        hql = hql + " and 1=1  ";

        if (null != groupBy) {
            hql = hql + " group by " + groupBy;
        }

        final String queryString = hql;
        final List hqlList = queryList;

        List list = getHibernateTemplate().executeFind(new HibernateCallback() {
            public Object doInHibernate(Session session)
                    throws HibernateException, SQLException {
                Query q = session.createQuery(queryString);
                if (resultsPerGroup != null) {
                    q.setMaxResults(new Integer(resultsPerGroup).intValue());
                }
                if (firstResultNo != null) {
                    q.setFirstResult(new Integer(firstResultNo).intValue());
                }
                for (int i = 0; i < hqlList.size(); i++) {
                    q.setParameter(i, hqlList.get(i));

                }
                List page = q.list();
                return page;
            }
        });
        return list;
    }

    /**
     * 
     * 返回分页信息 将分页信息设置到QueryResults中
     * 
     * @param queryStruct
     *            查询参数结构体
     * @param queryResults
     *            查询结果对象
     * @return queryResults 设置了分页信息的查询结果对象
     */
    protected <T> QueryResults<T> queryPageInfo(QueryStruct queryStruct,
            QueryResults<T> queryResults) {
        if (queryStruct.getFirstResultNo() != null
                && queryStruct.getResultsPerGroup() != null
                && queryStruct.getGroupBy() == null) {
            queryResults.setFirstResultNo(new Integer(queryStruct
                    .getFirstResultNo()));
            queryResults.setResultsPerGroup(new Integer(queryStruct
                    .getResultsPerGroup()));
            queryStruct.setFirstResultNo(null);
            queryStruct.setResultsPerGroup(null);
            // 楼毅添加于2011-03-09
            queryStruct.setGroupBy(null);
            List<Long> numberList = null;
            try {
                numberList = countByParameterGroup(queryStruct);
            } catch (Exception e) {
                
            }
            if (numberList != null && numberList.size() > 0) {
                // 由于没有分组条件，只返回一条数据此次查询的总记录数
                queryResults.setTotalResultsNum(numberList.get(0));
                // 返回开始记录数和每组记录数
            }
        }
        return queryResults;
    }
}
